package com.ddone.code.executor.service.impl;

import com.ddone.code.executor.pojo.entity.ColumnMetaData;
import com.ddone.code.executor.pojo.entity.DataSourceConfig;
import com.ddone.code.executor.pojo.entity.ExecuteResult;
import com.ddone.code.executor.pojo.enums.ExecutorType;
import com.ddone.code.executor.service.ExecutorService;
import lombok.SneakyThrows;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author ddone
 * @date 2024/8/2-20:58
 */
@Service
public class MysqlExecutorServiceImpl implements ExecutorService {
    @Resource
    @Qualifier("mysqlJdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @Override
    public ExecuteResult execute(String code, ExecutorType executeType) {
        List<Map<String, Object>> result = jdbcTemplate.queryForList(code);
        Map<String, ColumnMetaData> schemaInfo = new LinkedHashMap<>();
        jdbcTemplate.query(code, new ResultSetExtractor<Void>() {
            @SneakyThrows
            @Override
            public Void extractData(ResultSet rs) throws SQLException, DataAccessException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                // Get schema information
                for (int i = 1; i <= columnCount; i++) {
                    ColumnMetaData columnMetaData = ColumnMetaData
                            .builder()
                            .columnName(metaData.getColumnName(i))
                            .columnType(metaData.getColumnTypeName(i))
                            .columnSize(String.valueOf(metaData.getColumnDisplaySize(i)))
                            .tableName(metaData.getTableName(i))
                            .databaseName(metaData.getCatalogName(i))
                            .columnComment("字段备注")
                            .isNullable(metaData.isNullable(i) == ResultSetMetaData.columnNullable)
                            .build();
                    schemaInfo.put(metaData.getColumnName(i), columnMetaData);
                }
                return null;
            }
        });

        for (String key : schemaInfo.keySet()) {
            ColumnMetaData columnMetaData = schemaInfo.get(key);
            columnMetaData.setColumnComment(getColumnComment(
                    columnMetaData.getDatabaseName(),
                    columnMetaData.getTableName(),
                    columnMetaData.getColumnName()));
        }
        return ExecuteResult.builder()
                .executorType(executeType)
                .metaData(schemaInfo)
                .data(result)
                .build();
    }

    @Override
    public List<String> executorTableCompleter(DataSourceConfig dataSourceConfig,String schemaName) {
        String query = "select\n" +
                "    table_name\n" +
                "from information_schema.TABLES\n" +
                "where table_schema = ? ";
        try {
            return jdbcTemplate.queryForList(query,new Object[]{schemaName}, String.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    @Override
    public List<ColumnMetaData> tableColumnsCompleter(DataSourceConfig dataSourceConfig,
                                                      String schemaName,
                                                      String tableName) {

        String query = "select\n" +
                "    column_name,\n" +
                "    column_type,\n" +
                "    character_maximum_length,\n" +
                "    is_nullable,\n" +
                "    table_name,\n" +
                "    table_schema,\n" +
                "    column_comment\n" +
                "from information_schema.columns\n" +
                "where table_schema = ? and table_name = ?";
        try {
            List<Map<String, Object>> result = jdbcTemplate.queryForList(query, schemaName, tableName);

            // 将结果映射到 ColumnMetaData 列表
            return result.stream().map(item -> {
                return ColumnMetaData.builder()
                        .columnName(String.valueOf(item.get("column_name")).toLowerCase(Locale.ROOT))
                        .columnType((String) item.get("column_type"))
                        .columnSize(item.get("character_maximum_length")+"")
                        .isNullable(Objects.equals((String) item.get("is_nullable"), "YES"))
                        .tableName((String) item.get("table_name"))
                        .databaseName((String) item.get("table_schema"))
                        .columnComment((String) item.get("column_comment"))
                        .build();
            }).collect(Collectors.toList());
        } catch (DataAccessException e) {
           throw new RuntimeException(e);
        }
    }
    @Override
    public List<String> executorSchemaCompleter(DataSourceConfig dataSourceConfig) {
        String query = "SELECT SCHEMA_NAME FROM information_schema.schemata";
        try {
            return jdbcTemplate.queryForList(query, String.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    private String getColumnComment(String databaseName,String tableName, String columnName) {
        String columnComment = "";

        String query = "SELECT COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ? AND TABLE_SCHEMA = ?";
        try {
            return jdbcTemplate.queryForObject(query, new Object[]{tableName, columnName, databaseName}, String.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return columnComment;
    }
}
